{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "breathing-haiti",
   "metadata": {},
   "source": [
    "# Get the electricity energy mix data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "consistent-stocks",
   "metadata": {},
   "source": [
    "We use https://www.globalpetrolprices.com/energy_mix.php that use calculations based on data from the U.S. Energy Information Administration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "industrial-length",
   "metadata": {},
   "outputs": [],
   "source": [
    "# hide\n",
    "# Permet d'afficher plusieurs sorties dans une même cellule de Jupyter\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hispanic-columbus",
   "metadata": {},
   "source": [
    "https://www.globalpetrolprices.com/energy_mix.php\n",
    "\n",
    "Data is from 2018 to 2020 depending of the country.\n",
    "\n",
    "https://unfccc.int/ghg-inventories-annex-i-parties/2021 is maybe better but more difficult to use.\n",
    "\n",
    "https://www.globalpetrolprices.com/custom_graphic_ge.php?requestType=2&type=1&from=2019&to=2021&width=730&height=400&widthPercent=80&heightPercent=60&legend=bottom&indicator_url=fossil_fuels_electricity_generation,wind_electricity_generation,solar_electricity_generation,hydroelectricity_generation,nuclear_power_generation,geothermal_electricity_generation&unit=194"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "computational-treasurer",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_url = \"https://www.globalpetrolprices.com/custom_graphic_ge.php?requestType=2&type=1&from=1980&to=2021&width=730&height=400&widthPercent=80&heightPercent=60&legend=bottom&indicator_url=fossil_fuels_electricity_generation,wind_electricity_generation,solar_electricity_generation,hydroelectricity_generation,nuclear_power_generation,geothermal_electricity_generation&unit=\"\n",
    "countries = [107,179,52,53,2,3,108,4,160,181,5,6,109,110,7,182,183,8,54,9,111,10,184,55,11,56,185,57,113,58,114,59,13,60,61,62,15,115,16,63,186,18,187,188,189,190,64,19,20,21,65,22,66,67,191,68,192,162,193,194,69,70,119,195,71,196,197,26,27,72,73,28,29,30,120,199,200,121,122,123,124,201,125,202,74,31,126,127,128,75,164,129,130,76,203,131,77,78,79,205,206,132,80,81,133,134,82,208,83,84,32,209,137,211,212,86,87,88,138,213,166,167,34,89,90,139,207,214,140,141,35,171,36,37,143,215,216,39,144,91,217,218,145,42,44,172,92,146,93,220,94,95,147,221,222,173,96,97,148,223,149,98,45,99,224,225,150,151,152,100,153,101,175,46,102,226,154,103,227,155,228,49,48,156,177,51,157,158,105,106]\n",
    "country_id = 179\n",
    "import requests\n",
    "import pandas as pd\n",
    "from time import sleep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "opposite-tenant",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "190"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(countries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "improved-charles",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_url(country_id):\n",
    "    # Retreive the content\n",
    "    try:\n",
    "        r = requests.get(base_url + str(country_id))\n",
    "        content = r.text\n",
    "        if r.status_code != 200:\n",
    "            print(f'ERROR HTTP : {r.status_code} for {url}')\n",
    "            return None\n",
    "        if len(r.content) < 1_000:\n",
    "            print(f'ERROR HTTP content too small : {content} for {url}')\n",
    "            return None\n",
    "        return content\n",
    "    except:\n",
    "        logger.error(f'Unexpected ERROR for {url}: {sys.exc_info()[0]}')\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "public-drink",
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = {\n",
    "    \"107\":\"Afghanistan\",\n",
    "    \"179\":\"Albania\",\n",
    "    \"52\":\"Algeria\",\n",
    "    \"53\":\"Angola\",\n",
    "    \"2\":\"Antigua and Barbuda\",\n",
    "    \"3\":\"Argentina\",\n",
    "    \"108\":\"Armenia\",\n",
    "    \"4\":\"Aruba\",\n",
    "    \"160\":\"Australia\",\n",
    "    \"181\":\"Austria\",\n",
    "    \"5\":\"Azerbaijan\",\n",
    "    \"6\":\"Bahamas\",\n",
    "    \"109\":\"Bahrain\",\n",
    "    \"110\":\"Bangladesh\",\n",
    "    \"7\":\"Barbados\",\n",
    "    \"182\":\"Belarus\",\n",
    "    \"183\":\"Belgium\",\n",
    "    \"8\":\"Belize\",\n",
    "    \"54\":\"Benin\",\n",
    "    \"9\":\"Bermuda\",\n",
    "    \"111\":\"Bhutan\",\n",
    "    \"10\":\"Bolivia\",\n",
    "    \"184\":\"Bosnia and Herzegovina\",\n",
    "    \"55\":\"Botswana\",\n",
    "    \"11\":\"Brazil\",\n",
    "    \"56\":\"Brunei\",\n",
    "    \"185\":\"Bulgaria\",\n",
    "    \"57\":\"Burkina Faso\",\n",
    "    \"113\":\"Burma (Myanmar)\",\n",
    "    \"58\":\"Burundi\",\n",
    "    \"114\":\"Cambodia\",\n",
    "    \"59\":\"Cameroon\",\n",
    "    \"13\":\"Canada\",\n",
    "    \"60\":\"Cape Verde\",\n",
    "    \"61\":\"Central African Republic\",\n",
    "    \"62\":\"Chad\",\n",
    "    \"15\":\"Chile\",\n",
    "    \"115\":\"China\",\n",
    "    \"16\":\"Colombia\",\n",
    "    \"63\":\"Comoros\",\n",
    "    \"186\":\"Croatia\",\n",
    "    \"18\":\"Cuba\",\n",
    "    \"187\":\"Cyprus\",\n",
    "    \"188\":\"Czechia\",\n",
    "    \"189\":\"Democratic Republic of the Congo\",\n",
    "    \"190\":\"Denmark\",\n",
    "    \"64\":\"Djibouti\",\n",
    "    \"19\":\"Dominica\",\n",
    "    \"20\":\"Dominican Republic\",\n",
    "    \"21\":\"Ecuador\",\n",
    "    \"65\":\"Egypt\",\n",
    "    \"22\":\"El Salvador\",\n",
    "    \"66\":\"Equatorial Guinea\",\n",
    "    \"67\":\"Eritrea\",\n",
    "    \"191\":\"Estonia\",\n",
    "    \"68\":\"Ethiopia\",\n",
    "    \"192\":\"Faroe Islands\",\n",
    "    \"162\":\"Fiji\",\n",
    "    \"193\":\"Finland\",\n",
    "    \"194\":\"France\",\n",
    "    \"69\":\"Gabon\",\n",
    "    \"70\":\"Gambia\",\n",
    "    \"119\":\"Georgia\",\n",
    "    \"195\":\"Germany\",\n",
    "    \"71\":\"Ghana\",\n",
    "    \"196\":\"Gibraltar\",\n",
    "    \"197\":\"Greece\",\n",
    "    \"26\":\"Grenada\",\n",
    "    \"27\":\"Guatemala\",\n",
    "    \"72\":\"Guinea\",\n",
    "    \"73\":\"Guinea-Bissau\",\n",
    "    \"28\":\"Guyana\",\n",
    "    \"29\":\"Haiti\",\n",
    "    \"30\":\"Honduras\",\n",
    "    \"120\":\"Hong Kong\",\n",
    "    \"199\":\"Hungary\",\n",
    "    \"200\":\"Iceland\",\n",
    "    \"121\":\"India\",\n",
    "    \"122\":\"Indonesia\",\n",
    "    \"123\":\"Iran\",\n",
    "    \"124\":\"Iraq\",\n",
    "    \"201\":\"Ireland\",\n",
    "    \"125\":\"Israel\",\n",
    "    \"202\":\"Italy\",\n",
    "    \"74\":\"Ivory Coast\",\n",
    "    \"31\":\"Jamaica\",\n",
    "    \"126\":\"Japan\",\n",
    "    \"127\":\"Jordan\",\n",
    "    \"128\":\"Kazakhstan\",\n",
    "    \"75\":\"Kenya\",\n",
    "    \"164\":\"Kiribati\",\n",
    "    \"129\":\"Kuwait\",\n",
    "    \"130\":\"Kyrgyzstan\",\n",
    "    \"76\":\"Laos\",\n",
    "    \"203\":\"Latvia\",\n",
    "    \"131\":\"Lebanon\",\n",
    "    \"77\":\"Lesotho\",\n",
    "    \"78\":\"Liberia\",\n",
    "    \"79\":\"Libya\",\n",
    "    \"205\":\"Lithuania\",\n",
    "    \"206\":\"Luxembourg\",\n",
    "    \"132\":\"Macao\",\n",
    "    \"80\":\"Madagascar\",\n",
    "    \"81\":\"Malawi\",\n",
    "    \"133\":\"Malaysia\",\n",
    "    \"134\":\"Maldives\",\n",
    "    \"82\":\"Mali\",\n",
    "    \"208\":\"Malta\",\n",
    "    \"83\":\"Mauritania\",\n",
    "    \"84\":\"Mauritius\",\n",
    "    \"32\":\"Mexico\",\n",
    "    \"209\":\"Moldova\",\n",
    "    \"137\":\"Mongolia\",\n",
    "    \"211\":\"Montenegro\",\n",
    "    \"212\":\"Montserrat\",\n",
    "    \"86\":\"Morocco\",\n",
    "    \"87\":\"Mozambique\",\n",
    "    \"88\":\"Namibia\",\n",
    "    \"138\":\"Nepal\",\n",
    "    \"213\":\"Netherlands\",\n",
    "    \"166\":\"New Caledonia\",\n",
    "    \"167\":\"New Zealand\",\n",
    "    \"34\":\"Nicaragua\",\n",
    "    \"89\":\"Niger\",\n",
    "    \"90\":\"Nigeria\",\n",
    "    \"139\":\"North Korea\",\n",
    "    \"207\":\"North Macedonia\",\n",
    "    \"214\":\"Norway\",\n",
    "    \"140\":\"Oman\",\n",
    "    \"141\":\"Pakistan\",\n",
    "    \"35\":\"Panama\",\n",
    "    \"171\":\"Papua New Guinea\",\n",
    "    \"36\":\"Paraguay\",\n",
    "    \"37\":\"Peru\",\n",
    "    \"143\":\"Philippines\",\n",
    "    \"215\":\"Poland\",\n",
    "    \"216\":\"Portugal\",\n",
    "    \"39\":\"Puerto Rico\",\n",
    "    \"144\":\"Qatar\",\n",
    "    \"91\":\"Republic of the Congo\",\n",
    "    \"217\":\"Romania\",\n",
    "    \"218\":\"Russia\",\n",
    "    \"145\":\"Rwanda\",\n",
    "    \"42\":\"Saint Lucia\",\n",
    "    \"44\":\"Saint Vincent and the Grenadines\",\n",
    "    \"172\":\"Samoa\",\n",
    "    \"92\":\"Sao Tome and Principe\",\n",
    "    \"146\":\"Saudi Arabia\",\n",
    "    \"93\":\"Senegal\",\n",
    "    \"220\":\"Serbia\",\n",
    "    \"94\":\"Seychelles\",\n",
    "    \"95\":\"Sierra Leone\",\n",
    "    \"147\":\"Singapore\",\n",
    "    \"221\":\"Slovakia\",\n",
    "    \"222\":\"Slovenia\",\n",
    "    \"173\":\"Solomon Islands\",\n",
    "    \"96\":\"Somalia\",\n",
    "    \"97\":\"South Africa\",\n",
    "    \"148\":\"South Korea\",\n",
    "    \"223\":\"Spain\",\n",
    "    \"149\":\"Sri Lanka\",\n",
    "    \"98\":\"Sudan\",\n",
    "    \"45\":\"Suriname\",\n",
    "    \"99\":\"Swaziland\",\n",
    "    \"224\":\"Sweden\",\n",
    "    \"225\":\"Switzerland\",\n",
    "    \"150\":\"Syria\",\n",
    "    \"151\":\"Taiwan\",\n",
    "    \"152\":\"Tajikistan\",\n",
    "    \"100\":\"Tanzania\",\n",
    "    \"153\":\"Thailand\",\n",
    "    \"101\":\"Togo\",\n",
    "    \"175\":\"Tonga\",\n",
    "    \"46\":\"Trinidad and Tobago\",\n",
    "    \"102\":\"Tunisia\",\n",
    "    \"226\":\"Turkey\",\n",
    "    \"154\":\"Turkmenistan\",\n",
    "    \"103\":\"Uganda\",\n",
    "    \"227\":\"Ukraine\",\n",
    "    \"155\":\"United Arab Emirates\",\n",
    "    \"228\":\"United Kingdom\",\n",
    "    \"49\":\"Uruguay\",\n",
    "    \"48\":\"USA\",\n",
    "    \"156\":\"Uzbekistan\",\n",
    "    \"177\":\"Vanuatu\",\n",
    "    \"51\":\"Venezuela\",\n",
    "    \"157\":\"Vietnam\",\n",
    "    \"158\":\"Yemen\",\n",
    "    \"105\":\"Zambia\",\n",
    "    \"106\":\"Zimbabwe\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adult-setting",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "dense-milan",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_df(country_id):\n",
    "    print(\"Processing\", countries[str(country_id)])\n",
    "    r = get_url(country_id)\n",
    "    begin = r.find('arrayToDataTable')+len('arrayToDataTable')+15\n",
    "    end = r.find('options') - 31\n",
    "    data = r[begin:end]\n",
    "    data = data.split('],[')\n",
    "    data_clean = data.copy()\n",
    "    for i, d in enumerate(data):\n",
    "        d = d.replace(\"'\", \"\").replace(\"]\", \"\").replace(\"[\", \"\")\n",
    "        line = d.split(\",\")\n",
    "        # line[0] = line[0].replace(\"'\", \"\")\n",
    "        data_clean[i]=line\n",
    "    df = pd.DataFrame(data_clean[1:], columns=data_clean[0])\n",
    "    df['country_id'] = country_id\n",
    "    df['country_name'] = countries[str(country_id)]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "liked-honor",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing Qatar\n",
      "Processing France\n"
     ]
    }
   ],
   "source": [
    "df_qatar = get_df(144)\n",
    "df_france = get_df(194)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "hydraulic-preference",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Fossil fuels electricity generation</th>\n",
       "      <th>Geothermal electricity generation</th>\n",
       "      <th>Hydroelectricity generation</th>\n",
       "      <th>Nuclear power generation</th>\n",
       "      <th>Solar electricity generation</th>\n",
       "      <th>Wind electricity generation</th>\n",
       "      <th>country_id</th>\n",
       "      <th>country_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980</td>\n",
       "      <td>2.276</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981</td>\n",
       "      <td>2.596</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982</td>\n",
       "      <td>2.893</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Fossil fuels electricity generation Geothermal electricity generation  \\\n",
       "0  1980                               2.276                                 0   \n",
       "1  1981                               2.596                                 0   \n",
       "2  1982                               2.893                                 0   \n",
       "\n",
       "  Hydroelectricity generation Nuclear power generation  \\\n",
       "0                     0.00000                  0.00000   \n",
       "1                     0.00000                  0.00000   \n",
       "2                     0.00000                  0.00000   \n",
       "\n",
       "  Solar electricity generation Wind electricity generation  country_id  \\\n",
       "0                            0                           0         144   \n",
       "1                            0                           0         144   \n",
       "2                            0                           0         144   \n",
       "\n",
       "  country_name  \n",
       "0        Qatar  \n",
       "1        Qatar  \n",
       "2        Qatar  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_qatar.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "rural-agenda",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Fossil fuels electricity generation</th>\n",
       "      <th>Geothermal electricity generation</th>\n",
       "      <th>Hydroelectricity generation</th>\n",
       "      <th>Nuclear power generation</th>\n",
       "      <th>Solar electricity generation</th>\n",
       "      <th>Wind electricity generation</th>\n",
       "      <th>country_id</th>\n",
       "      <th>country_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1995</td>\n",
       "      <td>5.617</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>2001</td>\n",
       "      <td>9.354</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1985</td>\n",
       "      <td>52.251</td>\n",
       "      <td>0</td>\n",
       "      <td>60.24800</td>\n",
       "      <td>211.19200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2018</td>\n",
       "      <td>44.4009</td>\n",
       "      <td>0.117</td>\n",
       "      <td>64.48500</td>\n",
       "      <td>393.20001</td>\n",
       "      <td>10.569</td>\n",
       "      <td>28.599</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2020</td>\n",
       "      <td>44.553</td>\n",
       "      <td>0.155753680282</td>\n",
       "      <td>60.75027</td>\n",
       "      <td>335.41461</td>\n",
       "      <td>14.1455621272</td>\n",
       "      <td>43.5316709709</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2019</td>\n",
       "      <td>48.09322</td>\n",
       "      <td>0.16</td>\n",
       "      <td>56.98242</td>\n",
       "      <td>382.40302</td>\n",
       "      <td>11.668</td>\n",
       "      <td>37.429475</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1983</td>\n",
       "      <td>3.116</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2004</td>\n",
       "      <td>51.52328</td>\n",
       "      <td>0</td>\n",
       "      <td>59.27600</td>\n",
       "      <td>425.82901</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.594</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>2002</td>\n",
       "      <td>10.284</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980</td>\n",
       "      <td>117.986</td>\n",
       "      <td>0</td>\n",
       "      <td>68.25300</td>\n",
       "      <td>63.42400</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>194</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year Fossil fuels electricity generation  \\\n",
       "56  1995                               5.617   \n",
       "62  2001                               9.354   \n",
       "5   1985                              52.251   \n",
       "38  2018                             44.4009   \n",
       "40  2020                              44.553   \n",
       "39  2019                            48.09322   \n",
       "44  1983                               3.116   \n",
       "24  2004                            51.52328   \n",
       "63  2002                              10.284   \n",
       "0   1980                             117.986   \n",
       "\n",
       "   Geothermal electricity generation Hydroelectricity generation  \\\n",
       "56                                 0                     0.00000   \n",
       "62                                 0                     0.00000   \n",
       "5                                  0                    60.24800   \n",
       "38                             0.117                    64.48500   \n",
       "40                    0.155753680282                    60.75027   \n",
       "39                              0.16                    56.98242   \n",
       "44                                 0                     0.00000   \n",
       "24                                 0                    59.27600   \n",
       "63                                 0                     0.00000   \n",
       "0                                  0                    68.25300   \n",
       "\n",
       "   Nuclear power generation Solar electricity generation  \\\n",
       "56                  0.00000                            0   \n",
       "62                  0.00000                            0   \n",
       "5                 211.19200                            0   \n",
       "38                393.20001                       10.569   \n",
       "40                335.41461                14.1455621272   \n",
       "39                382.40302                       11.668   \n",
       "44                  0.00000                            0   \n",
       "24                425.82901                        0.009   \n",
       "63                  0.00000                            0   \n",
       "0                  63.42400                            0   \n",
       "\n",
       "   Wind electricity generation  country_id country_name  \n",
       "56                           0         144        Qatar  \n",
       "62                           0         144        Qatar  \n",
       "5                            0         194       France  \n",
       "38                      28.599         194       France  \n",
       "40               43.5316709709         194       France  \n",
       "39                   37.429475         194       France  \n",
       "44                           0         144        Qatar  \n",
       "24                       0.594         194       France  \n",
       "63                           0         144        Qatar  \n",
       "0                            0         194       France  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_france, df_qatar], ignore_index=True).sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "complex-cowboy",
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm world_energy_mix.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "sudden-shipping",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing Afghanistan\n",
      "Processing Albania\n",
      "Processing Algeria\n",
      "Processing Angola\n",
      "Processing Antigua and Barbuda\n",
      "Processing Argentina\n",
      "Processing Armenia\n",
      "Processing Aruba\n",
      "Processing Australia\n",
      "Processing Austria\n",
      "Processing Azerbaijan\n",
      "Processing Bahamas\n",
      "Processing Bahrain\n",
      "Processing Bangladesh\n",
      "Processing Barbados\n",
      "Processing Belarus\n",
      "Processing Belgium\n",
      "Processing Belize\n",
      "Processing Benin\n",
      "Processing Bermuda\n",
      "Processing Bhutan\n",
      "Processing Bolivia\n",
      "Processing Bosnia and Herzegovina\n",
      "Processing Botswana\n",
      "Processing Brazil\n",
      "Processing Brunei\n",
      "Processing Bulgaria\n",
      "Processing Burkina Faso\n",
      "Processing Burma (Myanmar)\n",
      "Processing Burundi\n",
      "Processing Cambodia\n",
      "Processing Cameroon\n",
      "Processing Canada\n",
      "Processing Cape Verde\n",
      "Processing Central African Republic\n",
      "Processing Chad\n",
      "Processing Chile\n",
      "Processing China\n",
      "Processing Colombia\n",
      "Processing Comoros\n",
      "Processing Croatia\n",
      "Processing Cuba\n",
      "Processing Cyprus\n",
      "Processing Czechia\n",
      "Processing Democratic Republic of the Congo\n",
      "Processing Denmark\n",
      "Processing Djibouti\n",
      "Processing Dominica\n",
      "Processing Dominican Republic\n",
      "Processing Ecuador\n",
      "Processing Egypt\n",
      "Processing El Salvador\n",
      "Processing Equatorial Guinea\n",
      "Processing Eritrea\n",
      "Processing Estonia\n",
      "Processing Ethiopia\n",
      "Processing Faroe Islands\n",
      "Processing Fiji\n",
      "Processing Finland\n",
      "Processing France\n",
      "Processing Gabon\n",
      "Processing Gambia\n",
      "Processing Georgia\n",
      "Processing Germany\n",
      "Processing Ghana\n",
      "Processing Gibraltar\n",
      "Processing Greece\n",
      "Processing Grenada\n",
      "Processing Guatemala\n",
      "Processing Guinea\n",
      "Processing Guinea-Bissau\n",
      "Processing Guyana\n",
      "Processing Haiti\n",
      "Processing Honduras\n",
      "Processing Hong Kong\n",
      "Processing Hungary\n",
      "Processing Iceland\n",
      "Processing India\n",
      "Processing Indonesia\n",
      "Processing Iran\n",
      "Processing Iraq\n",
      "Processing Ireland\n",
      "Processing Israel\n",
      "Processing Italy\n",
      "Processing Ivory Coast\n",
      "Processing Jamaica\n",
      "Processing Japan\n",
      "Processing Jordan\n",
      "Processing Kazakhstan\n",
      "Processing Kenya\n",
      "Processing Kiribati\n",
      "Processing Kuwait\n",
      "Processing Kyrgyzstan\n",
      "Processing Laos\n",
      "Processing Latvia\n",
      "Processing Lebanon\n",
      "Processing Lesotho\n",
      "Processing Liberia\n",
      "Processing Libya\n",
      "Processing Lithuania\n",
      "Processing Luxembourg\n",
      "Processing Macao\n",
      "Processing Madagascar\n",
      "Processing Malawi\n",
      "Processing Malaysia\n",
      "Processing Maldives\n",
      "Processing Mali\n",
      "Processing Malta\n",
      "Processing Mauritania\n",
      "Processing Mauritius\n",
      "Processing Mexico\n",
      "Processing Moldova\n",
      "Processing Mongolia\n",
      "Processing Montenegro\n",
      "Processing Montserrat\n",
      "Processing Morocco\n",
      "Processing Mozambique\n",
      "Processing Namibia\n",
      "Processing Nepal\n",
      "Processing Netherlands\n",
      "Processing New Caledonia\n",
      "Processing New Zealand\n",
      "Processing Nicaragua\n",
      "Processing Niger\n",
      "Processing Nigeria\n",
      "Processing North Korea\n",
      "Processing North Macedonia\n",
      "Processing Norway\n",
      "Processing Oman\n",
      "Processing Pakistan\n",
      "Processing Panama\n",
      "Processing Papua New Guinea\n",
      "Processing Paraguay\n",
      "Processing Peru\n",
      "Processing Philippines\n",
      "Processing Poland\n",
      "Processing Portugal\n",
      "Processing Puerto Rico\n",
      "Processing Qatar\n",
      "Processing Republic of the Congo\n",
      "Processing Romania\n",
      "Processing Russia\n",
      "Processing Rwanda\n",
      "Processing Saint Lucia\n",
      "Processing Saint Vincent and the Grenadines\n",
      "Processing Samoa\n",
      "Processing Sao Tome and Principe\n",
      "Processing Saudi Arabia\n",
      "Processing Senegal\n",
      "Processing Serbia\n",
      "Processing Seychelles\n",
      "Processing Sierra Leone\n",
      "Processing Singapore\n",
      "Processing Slovakia\n",
      "Processing Slovenia\n",
      "Processing Solomon Islands\n",
      "Processing Somalia\n",
      "Processing South Africa\n",
      "Processing South Korea\n",
      "Processing Spain\n",
      "Processing Sri Lanka\n",
      "Processing Sudan\n",
      "Processing Suriname\n",
      "Processing Swaziland\n",
      "Processing Sweden\n",
      "Processing Switzerland\n",
      "Processing Syria\n",
      "Processing Taiwan\n",
      "Processing Tajikistan\n",
      "Processing Tanzania\n",
      "Processing Thailand\n",
      "Processing Togo\n",
      "Processing Tonga\n",
      "Processing Trinidad and Tobago\n",
      "Processing Tunisia\n",
      "Processing Turkey\n",
      "Processing Turkmenistan\n",
      "Processing Uganda\n",
      "Processing Ukraine\n",
      "Processing United Arab Emirates\n",
      "Processing United Kingdom\n",
      "Processing Uruguay\n",
      "Processing USA\n",
      "Processing Uzbekistan\n",
      "Processing Vanuatu\n",
      "Processing Venezuela\n",
      "Processing Vietnam\n",
      "Processing Yemen\n",
      "Processing Zambia\n",
      "Processing Zimbabwe\n"
     ]
    }
   ],
   "source": [
    "df_full = pd.DataFrame()\n",
    "for country_id in countries:\n",
    "    df = get_df(country_id)\n",
    "    df_full = pd.concat([df_full, df], ignore_index=True)\n",
    "    df_full.to_csv('world_energy_mix.csv')\n",
    "    sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "smooth-cooking",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Fossil fuels electricity generation</th>\n",
       "      <th>Geothermal electricity generation</th>\n",
       "      <th>Hydroelectricity generation</th>\n",
       "      <th>Nuclear power generation</th>\n",
       "      <th>Solar electricity generation</th>\n",
       "      <th>Wind electricity generation</th>\n",
       "      <th>country_id</th>\n",
       "      <th>country_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980</td>\n",
       "      <td>0.231</td>\n",
       "      <td>0</td>\n",
       "      <td>0.71100</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981</td>\n",
       "      <td>0.271</td>\n",
       "      <td>0</td>\n",
       "      <td>0.72100</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1982</td>\n",
       "      <td>0.245</td>\n",
       "      <td>0</td>\n",
       "      <td>0.70700</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1983</td>\n",
       "      <td>0.254</td>\n",
       "      <td>0</td>\n",
       "      <td>0.74600</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1984</td>\n",
       "      <td>0.262</td>\n",
       "      <td>0</td>\n",
       "      <td>0.75700</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7632</th>\n",
       "      <td>2015</td>\n",
       "      <td>4.31178</td>\n",
       "      <td>0</td>\n",
       "      <td>4.94000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7633</th>\n",
       "      <td>2016</td>\n",
       "      <td>3.71488</td>\n",
       "      <td>0</td>\n",
       "      <td>2.95500</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7634</th>\n",
       "      <td>2017</td>\n",
       "      <td>3.21856</td>\n",
       "      <td>0</td>\n",
       "      <td>3.92900</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7635</th>\n",
       "      <td>2018</td>\n",
       "      <td>3.93202</td>\n",
       "      <td>0</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7636</th>\n",
       "      <td>2019</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7637 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year Fossil fuels electricity generation  \\\n",
       "0     1980                               0.231   \n",
       "1     1981                               0.271   \n",
       "2     1982                               0.245   \n",
       "3     1983                               0.254   \n",
       "4     1984                               0.262   \n",
       "...    ...                                 ...   \n",
       "7632  2015                             4.31178   \n",
       "7633  2016                             3.71488   \n",
       "7634  2017                             3.21856   \n",
       "7635  2018                             3.93202   \n",
       "7636  2019                                null   \n",
       "\n",
       "     Geothermal electricity generation Hydroelectricity generation  \\\n",
       "0                                    0                     0.71100   \n",
       "1                                    0                     0.72100   \n",
       "2                                    0                     0.70700   \n",
       "3                                    0                     0.74600   \n",
       "4                                    0                     0.75700   \n",
       "...                                ...                         ...   \n",
       "7632                                 0                     4.94000   \n",
       "7633                                 0                     2.95500   \n",
       "7634                                 0                     3.92900   \n",
       "7635                                 0                     5.00000   \n",
       "7636                                 0                        null   \n",
       "\n",
       "     Nuclear power generation Solar electricity generation  \\\n",
       "0                     0.00000                            0   \n",
       "1                     0.00000                            0   \n",
       "2                     0.00000                            0   \n",
       "3                     0.00000                            0   \n",
       "4                     0.00000                            0   \n",
       "...                       ...                          ...   \n",
       "7632                  0.00000                        0.008   \n",
       "7633                  0.00000                        0.009   \n",
       "7634                  0.00000                        0.011   \n",
       "7635                  0.00000                        0.014   \n",
       "7636                  0.00000                        0.014   \n",
       "\n",
       "     Wind electricity generation country_id country_name  \n",
       "0                              0        107  Afghanistan  \n",
       "1                              0        107  Afghanistan  \n",
       "2                              0        107  Afghanistan  \n",
       "3                              0        107  Afghanistan  \n",
       "4                              0        107  Afghanistan  \n",
       "...                          ...        ...          ...  \n",
       "7632                           0        106     Zimbabwe  \n",
       "7633                           0        106     Zimbabwe  \n",
       "7634                           0        106     Zimbabwe  \n",
       "7635                           0        106     Zimbabwe  \n",
       "7636                           0        106     Zimbabwe  \n",
       "\n",
       "[7637 rows x 9 columns]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "blind-vancouver",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Year',\n",
       " 'Fossil fuels electricity generation',\n",
       " 'Geothermal electricity generation',\n",
       " 'Hydroelectricity generation',\n",
       " 'Nuclear power generation',\n",
       " 'Solar electricity generation',\n",
       " 'Wind electricity generation']"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "['1980', '0.716', '0', '2.91900', '0.00000', '0', '0']"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "['2019', '0', '0', '5.15394', '0.00000', '0.022', '0']"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "data_clean[0]\n",
    "data_clean[1]\n",
    "data_clean[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "treated-cheese",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_albania = pd.DataFrame(data_clean[1:], columns=data_clean[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "composite-mystery",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_albania['country_id']=179"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "stuffed-recorder",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "leximpact",
   "language": "python",
   "name": "leximpact"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
